
package com.huizhan.web.dao;
 
import org.springframework.data.domain.*;
import org.springframework.data.jpa.repository.*;
import org.springframework.stereotype.Repository;
import com.huizhan.web.model.*;
import java.util.*;

@Repository 
public interface OrderFormDao extends JpaRepository<OrderForm,Long>
{
    
    /**
     * 分页查询用户集合
     * @param name 姓名
     * @param phone 手机号
     * @param startDate 开始时间
     * @param endDate 结束时间
     * @param pageable 分页参数
     * @return 用户集合
     */
    @Query(
        value = "SELECT * FROM order_form"
            + " WHERE project_id = ?1"
            + " AND (name LIKE CONCAT('%',?2,'%') OR ?2  = '')"
            + " AND (phone LIKE CONCAT('%',?3,'%') OR ?3  = '' )"
            + " AND  unix_timestamp(create_date) between  ?4 and ?5" ,
        countQuery = "SELECT COUNT(1) FROM order_form"
        + " WHERE project_id = ?1"
        + " AND (name LIKE CONCAT('%',?2,'%') OR ?2  = '')"
        + " AND (phone LIKE CONCAT('%',?3,'%') OR ?3  = '' )"
        + " AND  unix_timestamp(create_date) between  ?4 and  ?5" ,
        nativeQuery = true
    )
    Page<OrderForm> findByWhere(String projectId,String name,String phone,Long startDate,Long endDate, Pageable pageable);
    
    @Query(value =
        "SELECT IFNULL(SUM(count),0) as count FROM order_form WHERE project_id = ?1 AND is_pay=1 AND unix_timestamp(create_date) between  ?2 and ?3 union all "+
        "SELECT IFNULL(SUM(count),0) as count FROM order_form WHERE project_id = ?1 AND is_pay=0 AND unix_timestamp(create_date) between  ?2 and ?3 union  all "+
        "SELECT IFNULL(SUM(amount),0) as count FROM order_form WHERE project_id = ?1 AND is_pay=1 AND unix_timestamp(create_date) between  ?2 and ?3 union  all "+
        "SELECT IFNULL(SUM(amount),0) as count FROM order_form WHERE project_id = ?1 AND is_pay=0 AND unix_timestamp(create_date) between  ?2 and ?3 union  all "+
        "SELECT IFNULL(SUM(number),0) as count  FROM ticket_record WHERE project_id = ?1 AND  type =0 AND unix_timestamp(create_date) between ?2 and ?3 union  all "+
        "SELECT IFNULL(SUM(r.number*t.price),0) FROM ticket_record r INNER JOIN ticket_type t ON t.ticket_type_id=r.ticket_type_id WHERE r.project_id = ?1 AND r.type =0 AND unix_timestamp(r.create_date) between ?2 and ?3",
        nativeQuery = true
    )
    List<Object> findByWhere(String projectId,Long startDate,Long endDate);

     /**
      * 根据订单微信编号获取订单
      * @param wechatPayGuid  微信编号
      * @return 订单
      */
      OrderForm findByWechatPayGuidAndOpenId(String wechatPayGuid,String openId);
      
     /**
      * 根据微信ID获取订单
      * @param openId  微信ID
      * @return 订单
      */
      List<OrderForm> findByOpenId(String openId);
}
